package edu.njfu.sas.dao.impl;

import com.sun.org.apache.xerces.internal.impl.xpath.XPath;
import edu.njfu.sas.dao.StudentDao;
import edu.njfu.sas.model.Student;
import edu.njfu.sas.util.C3p0Util;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.awt.peer.CanvasPeer;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class StudentDaoImpl3 implements StudentDao {
//    @Override
//    public boolean registerStudent(String userName, String password) {
//        String sql="insert into tbl_user (username, password) value (?,?);";
//        QueryRunner queryRunner=new QueryRunner(C3p0Util.getConnection());
//        try {
//            return  queryRunner.update(sql, new Object[]{userName, password})>0;
//        } catch (SQLException throwables) {
//            return false;
//        }
//    }

    @Override
    public List<Student> getAllStudent()  {
        String sql="select * from tbl_student";
        QueryRunner queryRunner=new QueryRunner(C3p0Util.getConnection());
        List<Student> students = null;
        try {
            students = queryRunner.query(sql,new BeanListHandler<Student>(Student.class));
        } catch (SQLException throwables) {
            students=null;
            return students;
        }
        return students;
    }
//模糊搜索返回List数组
    @Override
    public List<Student> getStudentByName(String name) {
        String sql="select * from tbl_student where stuname like  ?";
        List<Student> students=new ArrayList<Student>();
        QueryRunner queryRunner=new QueryRunner(C3p0Util.getConnection());
        try {
            students=queryRunner.query(sql,new BeanListHandler<Student>(Student.class),new Object[]{"%"+name+"%"});
        } catch (SQLException throwables) {

            students=null;
        }
        if (students.size()>0)
            return students;
        else
            return null;
    }

    @Override
    public boolean saveStdudent(Student s) {
        String sql="insert into tbl_student value (?,?,?,?,?,?,?,?);";
        QueryRunner queryRunner=new QueryRunner(C3p0Util.getConnection());
        Object[] userInfo = {
            s.getStuNo(),
                s.getStuName(),
                s.getClasses(),
                s.getGender(),
                s.getDepartment(),
                s.getTel(),
                s.getDormNo(),
                s.getPhotopath()
        };
        try {
            return  queryRunner.update(sql,userInfo)>0;
        } catch (SQLException throwables) {
            return  false;
        }
    }

    @Override
    public  Student getStudentByNo(String no) {
        String sql="select * from tbl_student where stuno=?";
        List students=new ArrayList<Student>();

        QueryRunner queryRunner=new QueryRunner(C3p0Util.getConnection());
        try {
            students=queryRunner.query(sql,new BeanListHandler<Student>(Student.class),new Object[]{no});
            if (students.size()>0)
                return (Student)students.get(0);
            else
                return null;
        } catch (SQLException throwables) {
//            throwables.printStackTrace();
            return null;
        }
    }

    @Override
    public boolean updateStudent(Student s) {
        String sql="update tbl_student set stuname=?,classes=?,gender=?,department=?,tel=?,dormno=?,photopath=? where stuno=?;";
        Object[] userInfo = {
                s.getStuName(),
                s.getClasses(),
                s.getGender(),
                s.getDepartment(),
                s.getTel(),
                s.getDormNo(),
                s.getPhotopath(),
                s.getStuNo()
        };
        System.out.println(userInfo.toString());
        QueryRunner queryRunner = new QueryRunner(C3p0Util.getConnection());
        try {
          return queryRunner.update(sql, userInfo)>0;
        } catch (SQLException throwables) {
            return false;
        }
    }

    @Override
    public boolean deleteStudentByNo(String no) {
        String sql="delete from tbl_student where stuno=?;";
        QueryRunner queryRunner=new QueryRunner(C3p0Util.getConnection());
        try {
           return queryRunner.update(sql,new Object[]{no})>0;

        } catch (SQLException throwables) {
            return false;
        }
    }

    @Override
    public List<Student> getStudentByPaging(int currentPage, int pageSize) {
        String sql="select * from tbl_student where stuno limit ?,?";
        int start=(currentPage-1)*pageSize;
        QueryRunner queryRunner=new QueryRunner(C3p0Util.getConnection());
        List<Student> students= null;
        try {
            students = queryRunner.query(sql,new BeanListHandler<Student>(Student.class),new Object[]{start,pageSize});
            return students;
        } catch (SQLException throwables) {
            return null;
        }

    }

    @Override
    public long totalRecords() {
        String sql="select count(*) as count from tbl_student";
        QueryRunner queryRunner=new QueryRunner(C3p0Util.getConnection());
        long result=0;
        try {
            List<Map<String,Object>> query=queryRunner.query(sql,new MapListHandler());
            result=(Long)query.get(0).get("count");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    public static void main(String[] args) {
        StudentDaoImpl3 st3 = new StudentDaoImpl3();
        Student student = new Student("12",
                "李四",
                "23434",
                "2",
                "23434",
                "23434",
                "23434",
                "23434"
                );

        System.out.println(st3.getStudentByName("王"));
    }
}

